﻿using LitJson2;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Windows.Forms;

namespace ETS_FinancialData_sync_tools
{
    public partial class Initialize : Form
    {
        public Initialize()
        {
            InitializeComponent();
            System.Windows.Forms.Control.CheckForIllegalCrossThreadCalls = false;
        }

        #region "声明变量"

        /// <summary>
        /// 写入INI文件
        /// </summary>
        /// <param name="section">节点名称[如[TypeName]]</param>
        /// <param name="key">键</param>
        /// <param name="val">值</param>
        /// <param name="filepath">文件路径</param>
        /// <returns></returns>
        [System.Runtime.InteropServices.DllImport("kernel32")]
        private static extern long WritePrivateProfileString(string section, string key, string val, string filepath);
        /// <summary>
        /// 读取INI文件
        /// </summary>
        /// <param name="section">节点名称</param>
        /// <param name="key">键</param>
        /// <param name="def">值</param>
        /// <param name="retval">stringbulider对象</param>
        /// <param name="size">字节大小</param>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        [DllImport("kernel32")]
        private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retval, int size, string filePath);

        private string strFilePath = Application.StartupPath + "\\lxerp.ini";//获取INI文件路径
        private string strSec = ""; //INI文件名

        #endregion

        #region "自定义读取INI文件中的内容方法"
        /// <summary>
        /// 自定义读取INI文件中的内容方法
        /// </summary>
        /// <param name="Section">键</param>
        /// <param name="key">值</param>
        /// <returns></returns>
        private string ContentValue(string Section, string key)
        {

            StringBuilder temp = new StringBuilder(1024);
            GetPrivateProfileString(Section, key, "", temp, 1024, strFilePath);
            return temp.ToString();
        }
        #endregion

        #region "获取sqlconn连接参数"

        private string sqlconn;

        public string Sqlconn
        {
            get { return sqlconn; }
            set { sqlconn = value; }
        }

        public void getsqlconn()
        {
            try
            {
                if (File.Exists(strFilePath))//读取时先要判读INI文件是否存在
                {

                    //strSec = Path.GetFileNameWithoutExtension(strFilePath);
                    strSec = "Connection";
                    string Database = ContentValue(strSec, "Database");
                    dbName = Database;
                    string ServerName = ContentValue(strSec, "ServerName");
                    string LogId = ContentValue(strSec, "LogId");
                    string Logpass = ContentValue(strSec, "Logpass");
                    sqlconn = @"server=" + ServerName + ";database = " + Database + "; uid =" + LogId + "; pwd = " + Logpass + ";";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion

        public string localid;
        public string remoteid;
        public int sync_id = 0;
        public int a = 0;//存放各个表的总记录数
        public int s = 0;//存放各个表的成功记录数
        public int e = 0;//存放各个表的失败记录数
        public string tableName = "";
        public string ip = "127.0.0.1";
        public string port = "8080";
        public string dbName = null;
        public int jsonQty = 20000;//每次操作的生成的json数
        public int plusQty = 0;//总数a对jsonQty取余数

        #region //根据传入的sql语句返回受影响的行数
        /// <summary>
        /// 根据传入的sql语句返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int exec_sql(string sql)
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(Sqlconn);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandTimeout = 50000;
                conn.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                saveErrLog(sql + ex.Message, "sql_err");
            }

            finally
            {
                conn.Close();
            }

            return result;
        }
        #endregion

        //保存post和get的json
        public void saveErrLog(string log, string txtName)
        {
            log = "--" + DateTime.Now.ToString() + " : \n" + log;
            if (File.Exists(Application.StartupPath + "\\" + txtName + ".txt"))
            {
                StreamWriter SW;
                SW = File.AppendText("" + txtName + ".txt");
                SW.WriteLine(log + "\r\n");
                SW.Close();
                //Console.WriteLine("Text Appended Successfully");
            }
            else
            {
                StreamWriter SW;
                SW = File.CreateText("" + txtName + ".txt");
                SW.WriteLine(log + "\r\n");
                SW.Close();
                listBox1.Items.Add("" + txtName + ".txt不存在，已经重新创建！");
            }
        }
        public void saveErrLog(string log, string txtName,string expandedName)
        {
            log = "--" + DateTime.Now.ToString() + " : \n" + log;
            //if (File.Exists(Application.StartupPath + "\\审计\\" + txtName + ".sql"))
            //{
            //    StreamWriter SW;
            //    SW = File.AppendText(Application.StartupPath + "\\审计\\" + txtName + ".sql");
            //    SW.WriteLine(log + "\r\n");
            //    SW.Close();
            //    //Console.WriteLine("Text Appended Successfully");
            //}
            //else
            //{
                StreamWriter SW;
                SW = File.CreateText(Application.StartupPath + "\\审计\\" + txtName + ".sql");
                SW.WriteLine(log + "\r\n");
                SW.Close();
                listBox1.Items.Add("" + txtName + ".sql已经重新创建！");
            //}
        }

        private void button14_Click(object sender, EventArgs e)
        {

            string setupSql =
          @"ALTER DATABASE [" + dbName + "] SET TRUSTWORTHY ON;";
            setupSql += "exec sp_changedbowner 'sa';";
            setupSql += @"exec sp_configure 'show advanced options', '1';
            reconfigure with override ;
            exec sp_configure 'clr enabled', '1';
            reconfigure with override; 
            exec sp_configure 'show advanced options', '1';";
            setupSql += " EXEC sp_dbcmptlevel [" + dbName + "], 100 ;";
            if (exec_sql(setupSql) == -1)
            {
                MessageBox.Show("clr 权限已经开启 ");
            }
        }
        private void Initialize_Load(object sender, EventArgs e)
        {

            //获取连接参数
            getsqlconn();

            //检查lxerp配置文件
            //并且显示配置信息
            checkLxerpSteup();

            //检查并且更新需要的表字段
            updateDBstructure();

            localid = LocalId.Text;
            remoteid = RemoteId.Text;
            CreateDataTable();

            listBox1.Items.Add("正在获取非过滤的项");

            getFilteringRules();
            Thread.Sleep(100);
            string showtext = "付款：";
            foreach (var item in paymentCodeArr)
            {
                showtext += "|" + item;
            }
            listBox1.Items.Add(showtext);
            showtext = "财务：";
            foreach (var item in kindaccCodeArr)
            {
                showtext += "|" + item;
            }
            listBox1.Items.Add(showtext);
        }
        DataTable dt;
        public void CreateDataTable()
        {
            try
            {
                dt = new DataTable("t_SyncFinancialRecords");
                dt.Columns.Add("sync_id");//, System.Type.GetType("System.String"));
                dt.Columns.Add("club_id");//, System.Type.GetType("System.Numeric"));
                dt.Columns.Add("sync_type");//, System.Type.GetType("System.Char"));
                dt.Columns.Add("data_type");//, System.Type.GetType("System.String"));
                dt.Columns.Add("fdate");//, System.Type.GetType("System.DateTime"));
                dt.Columns.Add("ftimestamp");//, System.Type.GetType("System.String"));
                dt.Columns.Add("fstatus");//, System.Type.GetType("System.Int32"));
                dt.Columns.Add("fjson");//, System.Type.GetType("System.String"));
                dt.Columns.Add("fdemo");//, System.Type.GetType("System.String"));
                dt.Columns.Add("SendCounts");//, System.Type.GetType("System.Int32"));
                dt.Columns.Add("LastSendTimes");//, System.Type.GetType("System.DateTime"));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n" + ex.StackTrace);
            }
        }
        /// <summary>
        /// 启动程序时候根据domain自动生成配置环境和正式环境的ini文件的内容
        /// </summary>
        public void checkLxerpSteup()
        {
            try
            {
                if (File.Exists(strFilePath))//读取时先要判读INI文件是否存在
                {
                    strSec = "ADMIN";
                    LocalId.Text = ContentValue(strSec, "LocalId");
                    RemoteId.Text = ContentValue(strSec, "RemoteId");
                    transferip.Text = ContentValue(strSec, "transferIP");
                    transferPort.Text = ContentValue(strSec, "transferPort");

                }
                else
                {
                    MessageBox.Show("lxerp.ini文件不存在！");
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show("请检查lxerp.ini文件下admin组是否有LocalId+RemoteId+transferIP+transferPort等值!\n\r" + ex.Message + "\n\r" + ex.StackTrace, "友情提示");
            }
        }
        //更新数据库结构
        private void updateDBstructure()
        {
            StringBuilder b = new StringBuilder();
            b.Append(@"
if (select count(*) from syscolumns where [id] = object_id('SYS_COMPANY') and [name] = 'LocalId') = 0
begin
   ALTER TABLE SYS_COMPANY  add LocalId varchar(20) NULL 
end 
if (select count(*) from syscolumns where [id] = object_id('SYS_COMPANY') and [name] = 'RemoteId') = 0
begin
   ALTER TABLE SYS_COMPANY  add RemoteId varchar(20) NULL
end
");
            //            if (select count(*) from syscolumns where [id] = object_id('pos_bills') and [name] = 'ReadytoSync') = 0
            //begin
            //   ALTER TABLE pos_bills  add ReadytoSync tinyint null
            //end
            //if (select count(*) from syscolumns where [id] = object_id('pos_sales') and [name] = 'ReadytoSync') = 0
            //begin
            //   ALTER TABLE pos_sales  add ReadytoSync tinyint null
            //end
            //if (select count(*) from syscolumns where [id] = object_id('fitness_checkin') and [name] = 'ReadytoSync') = 0
            //begin
            //   ALTER TABLE fitness_checkin  add ReadytoSync tinyint null
            //end
            //if (select count(*) from syscolumns where [id] = object_id('pos_bill_payment') and [name] = 'ReadytoSync') = 0
            //begin
            //   ALTER TABLE pos_bill_payment  add ReadytoSync tinyint null
            //end
            try
            {
                if (exec_sql(b.ToString()) == -1)
                {
                    saveErrLog("<<系统已帮助您升级部分表结构！...LocalId&&RemoteId&&ReadytoSync>>", "初始化");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " 表字段升级错误\n\r" + ex.StackTrace, "初始化错误");
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                strSec = "ADMIN";
                WritePrivateProfileString(strSec, "LocalId", LocalId.Text, strFilePath);
                WritePrivateProfileString(strSec, "RemoteId", RemoteId.Text, strFilePath);
                WritePrivateProfileString(strSec, "transferIP", transferip.Text, strFilePath);
                WritePrivateProfileString(strSec, "transferPort", transferPort.Text, strFilePath);
                if (exec_sql("update SYS_COMPANY set LocalId='" + LocalId.Text + "',RemoteId='" + RemoteId.Text + "'") > 0)
                    MessageBox.Show("保存成功！", "友情提示");
            }
            catch (Exception ex)
            {
                MessageBox.Show("保存失败!\n\r" + ex.Message + "\n\r" + ex.StackTrace, "友情提示");
            }
        }

        private void button15_Click(object sender, EventArgs e)
        {
            string assembly = null;
            assembly = "CREATE ASSEMBLY LitJSON2 FROM '" + Application.StartupPath + "\\LitJSON2.dll' WITH PERMISSION_SET = UNSAFE ";

            if (exec_sql(assembly) == -1)
            {
                MessageBox.Show("LitJSON2>>程序集已注册！");
            }
            else
            {
                MessageBox.Show("LitJSON2>>返回0条受影响的行数\n请手动核对是否已经注册程序集！");
            }
            assembly = "CREATE ASSEMBLY Dong8FinancialDataTriggers FROM '" + Application.StartupPath + "\\Dong8FinancialDataTriggers.dll' WITH PERMISSION_SET = UNSAFE ";
            if (exec_sql(assembly) == -1)
            {
                MessageBox.Show("Dong8FinancialDataTriggers>>程序集已注册！");
            }
            else
            {
                MessageBox.Show("Dong8FinancialDataTriggers>>返回0条受影响的行数\n请手动核对是否已经注册程序集！");
            }
        }

        private void button17_Click(object sender, EventArgs e)
        {

            string sql =
    @"DROP ASSEMBLY [Dong8FinancialDataTriggers]
      DROP ASSEMBLY [LitJSON2]";
            if (exec_sql(sql) == -1)
            {
                MessageBox.Show("删除程序集成功！");
            }
            else
            {
                MessageBox.Show("删除失败，详情请查看日志sql_err文件！");
            }
        }
        private List<string> getTableList()
        {
            List<string> table = new List<string>();
            foreach (Control item in tabPage3.Controls)
            {
                if (item is CheckBox)
                {
                    if (((CheckBox)item).Checked == true)
                        table.Add(item.Text);
                }
            }
            foreach (Control item in tabPage6.Controls)
            {
                if (item is CheckBox)
                {
                    if (((CheckBox)item).Checked == true)
                        table.Add(item.Text);
                }
            }
            foreach (Control item in tabPage7.Controls)
            {
                if (item is CheckBox)
                {
                    if (((CheckBox)item).Checked == true)
                        table.Add(item.Text);
                }
            }
            //table.Add("sys_workshop");
            //table.Add("sys_user");
            //table.Add("sys_payment");
            //table.Add("syskindacc");
            //table.Add("fitness_checkin");
            //table.Add("pos_bill_payment");
            //table.Add("pos_sales");
            //table.Add("pos_bills");
            //table.Add("fitness_cardchange");
            //table.Add("jxc_bm_ck");
            //table.Add("memcardlog");
            //table.Add("mem_member");
            //table.Add("fitness_enroll_qchange");
            //table.Add("sys_identity");
            //table.Add("fitness_classes");
            //table.Add("mem_kind");
            //table.Add("SnArt");
            //table.Add("gym_reserve");
            //table.Add("fitness_enroll");
            //table.Add("gym_period");
            //table.Add("t_fsl_sk_student05");
            //table.Add("gym_item");
            //table.Add("fitness_monthfee");
            //table.Add("gym_kind");
            //table.Add("jxc_dj_kc_2");
            //table.Add("jxc_dj_kc_zc_2");
            //table.Add("jxc_dj_kc_db_2");
            //table.Add("jxc_dj_xs_2");
            //table.Add("jxc_dj_gh_2");
            //table.Add("jxc_bm_sp");
            //table.Add("jxc_zb_sp_kc");
            //table.Add("gym_status");
            //table.Add("card_times_info");
            return table;
        }
        private void button16_Click(object sender, EventArgs e)
        {
            string sql = null;
            int count = 0;
            List<string> table = getTableList();
            if (table.Count == 0)
            {
                MessageBox.Show("没有选择表！");
                return;
            }
            //string text = "";
            //foreach (Control item in tabPage3.Controls)
            //{
            //    if (item is Button)
            //    {
            //        text += " table.Add(\"" + item.Text + "\");\n";
            //    }
            //}
            //foreach (Control item in tabPage6.Controls)
            //{
            //    if (item is Button)
            //    {
            //        text += " table.Add(\"" + item.Text + "\");\n";
            //    }
            //}
            //foreach (Control item in tabPage7.Controls)
            //{
            //    if (item is Button)
            //    {
            //        text += " table.Add(\"" + item.Text + "\");\n";
            //    }
            //}
            //MessageBox.Show(text);
            //return;
            //StringBuilder sb = new StringBuilder();
            //foreach (string table_name in table)
            //{
            //    sb.Append("truncate table " + table_name + ";\n");
            //}
            //MessageBox.Show(sb.ToString());
            //return;
            foreach (string table_name in table)
            {
                sql = "CREATE TRIGGER " + table_name + "_financial_method ON  " + table_name + " FOR INSERT, UPDATE, DELETE AS EXTERNAL NAME Dong8FinancialDataTriggers.CLRTriggers." + table_name + "_financial_method";
                if (exec_sql(sql) == -1)
                {
                    count++;
                }
                else
                {
                    MessageBox.Show(table_name + ">>返回0条受影响的行数\n请手动核对是否已经创建触发器！");
                }
            }
            MessageBox.Show("成功注册：" + count + "个触发器!");
        }

        private void button18_Click(object sender, EventArgs e)
        {
            string sql = string.Empty;
            if (getTableList().Count == 0)
            {
                MessageBox.Show("没有选择表！");
                return;
            }
            foreach (string table_name in getTableList())
            {
                sql += "DROP TRIGGER " + table_name + "_financial_method \n";
            }

            if (exec_sql(sql) == -1)
            {
                MessageBox.Show("删除触发器成功！");
            }
            else
            {
                MessageBox.Show("删除失败，详情请查看日志sql_err文件！");
            }
        }

        #region //查询要上传的记录数
        private int ExcuteScalarSQL(string sql)
        {
            int i = 0;
            getsqlconn();
            SqlConnection conn = new SqlConnection(Sqlconn);
            try
            {

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandTimeout = 50000;
                conn.Open();
                i = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return i;
        }
        #endregion

        private void pos_bills_DoWork(object sender, DoWorkEventArgs e)
        {
            DateTime dt1 = DateTime.Now;
            try
            {
                a = ExcuteScalarSQL("select count(*) from pos_bills --where billcode NOT IN (SELECT fdemo FROM dbo.t_SyncFinancialRecords WHERE data_type='pos_bills')");
                int i = 0;
                getsqlconn();
                SqlConnection cn = new SqlConnection(Sqlconn);
                cn.Open();
                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandText = " SET   NOCOUNT   ON";
                cmd.ExecuteScalar();
                cmd.CommandText = "select billcode,billtype,rec_status,bill_status,pay_status,billshop,saleshop,payshop,billdate,opentime,closedate,closetime,openuserid,closeuserid,dailysno,tableno,tableno_ext,noofguest,serverno,periodno,shiftno,closeshift,parentshop,grand_billcode,checkin_id,cardno,memno,memid,guest_identity,name,sex,billtotal,discountpercent,discounttotal,gratuitypercent,gratuitytotal,taxpercent,taxtotal,tipstotal,rebatetotal,totalpayable,paymethod,paydesp,printno,invoiceno,invoiceflag,generateflag,payflag,reception,saletype,lockstatus,sttime,endtime,fcount_hh,fcount_mm,fcditemno,otherstatus,club_id,memsid,sequence,keyno,modify,modifytime,clubcode,Fpcno,fclub,fEntityNo,ch_payno,fcreatedate,fmodifydate,fUserNo,n_last,ifdown,is_check,bm_ck,dzxh,checker,rq_check,cardbalance,zb_bm_ck,depositall,fcusttdno,syscode,f_paykind,fRemark,saleid,paycode,accuserid,fbillnohbaccno,fhbbillflash,fRemarkdoc,quitydje,ydallje,fydbillno,finmpyj from pos_bills --where billcode NOT IN (SELECT fdemo FROM dbo.t_SyncFinancialRecords WHERE data_type='pos_bills')";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    pos_bills t = new pos_bills();

                    t.sync_id = Guid.NewGuid().ToString("D");
                    t.LocalId = localid;
                    t.club_id = Convert.ToInt64(remoteid);
                    t.data_type = "pos_bills";
                    t.sync_type = 0;

                    t.modify = dr["modify"].ToString();
                    t.tipstotal = getDoubleString(dr, "tipstotal");
                    t.tableno = dr["tableno"].ToString();
                    t.discountpercent = getDoubleString(dr, "discountpercent");
                    t.opentime = dr["opentime"].ToString();
                    t.cardbalance = getDoubleString(dr, "cardbalance");
                    t.fmodifydate = dr["fmodifydate"].ToString();
                    t.ifdown = dr["ifdown"].ToString();
                    t.saletype = getIntString(dr, "saletype");
                    t.fcditemno = dr["fcditemno"].ToString();
                    t.saleshop = dr["saleshop"].ToString();
                    t.paymethod = dr["paymethod"].ToString();
                    t.ydallje = getDoubleString(dr, "ydallje");
                    t.openuserid = dr["openuserid"].ToString();
                    t.fcusttdno = dr["fcusttdno"].ToString();
                    t.gratuitytotal = getDoubleString(dr, "gratuitytotal");
                    t.memsid = dr["memsid"].ToString();
                    t.serverno = dr["serverno"].ToString();
                    t.name = dr["name"].ToString();
                    t.dzxh = getDoubleString(dr, "dzxh");
                    t.memno = dr["memno"].ToString();
                    t.fhbbillflash = getIntString(dr, "fhbbillflash");
                    t.endtime = dr["endtime"].ToString();
                    t.fRemark = dr["fRemark"].ToString();
                    t.closeuserid = dr["closeuserid"].ToString();
                    t.taxpercent = getDoubleString(dr, "taxpercent");
                    t.ch_payno = dr["ch_payno"].ToString();
                    t.sex = dr["sex"].ToString();
                    t.checker = dr["checker"].ToString();
                    t.closeshift = dr["closeshift"].ToString();
                    t.memid = getIntString(dr, "memid");
                    t.pay_status = getIntString(dr, "pay_status");
                    t.fRemarkdoc = dr["fRemarkdoc"].ToString();
                    t.fcount_hh = getDoubleString(dr, "fcount_hh");
                    t.payflag = getIntString(dr, "payflag");
                    t.closedate = dr["closedate"].ToString();
                    t.accuserid = dr["accuserid"].ToString();
                    t.otherstatus = getIntString(dr, "otherstatus");
                    t.discounttotal = getDoubleString(dr, "discounttotal");
                    t.Fpcno = dr["Fpcno"].ToString();
                    t.fUserNo = dr["fUserNo"].ToString();
                    t.billtype = getIntString(dr, "billtype");
                    t.is_check = getIntString(dr, "is_check");
                    t.checkin_id = dr["checkin_id"].ToString();
                    t.lockstatus = getIntString(dr, "lockstatus");
                    t.payshop = dr["payshop"].ToString();
                    t.fydbillno = dr["fydbillno"].ToString();
                    t.invoiceno = dr["invoiceno"].ToString();
                    t.sequence = getIntString(dr, "sequence");
                    t.syscode = dr["syscode"].ToString();
                    t.periodno = dr["periodno"].ToString();
                    t.fclub = dr["fclub"].ToString();
                    t.n_last = getDoubleString(dr, "n_last");
                    t.cardno = dr["cardno"].ToString();
                    t.rec_status = getIntString(dr, "rec_status");
                    t.sttime = dr["sttime"].ToString();
                    t.fbillnohbaccno = dr["fbillnohbaccno"].ToString();
                    t.invoiceflag = getIntString(dr, "invoiceflag");
                    t.saleid = dr["saleid"].ToString();
                    t.modifytime = dr["modifytime"].ToString();
                    t.rebatetotal = getDoubleString(dr, "rebatetotal");
                    t.billdate = dr["billdate"].ToString();
                    t.zb_bm_ck = dr["zb_bm_ck"].ToString();
                    t.parentshop = dr["parentshop"].ToString();
                    t.tableno_ext = dr["tableno_ext"].ToString();
                    t.fcreatedate = dr["fcreatedate"].ToString();
                    t.reception = dr["reception"].ToString();
                    t.guest_identity = dr["guest_identity"].ToString();
                    t.billshop = dr["billshop"].ToString();
                    t.quitydje = getDoubleString(dr, "quitydje");
                    t.paydesp = dr["paydesp"].ToString();
                    t.paycode = dr["paycode"].ToString();
                    t.clubcode = dr["clubcode"].ToString();
                    t.gratuitypercent = getDoubleString(dr, "gratuitypercent");
                    t.closetime = dr["closetime"].ToString();
                    t.depositall = getDoubleString(dr, "depositall");
                    //t.club_id = dr["club_id"].ToString();
                    t.noofguest = getIntString(dr, "noofguest");
                    t.grand_billcode = dr["grand_billcode"].ToString();
                    t.bm_ck = dr["bm_ck"].ToString();
                    t.billcode = dr["billcode"].ToString();
                    t.printno = getIntString(dr, "printno");
                    t.finmpyj = getDoubleString(dr, "finmpyj");
                    t.taxtotal = getDoubleString(dr, "taxtotal");
                    t.keyno = dr["keyno"].ToString();
                    t.dailysno = getIntString(dr, "dailysno");
                    t.f_paykind = dr["f_paykind"].ToString();
                    t.billtotal = getDoubleString(dr, "billtotal");
                    t.fEntityNo = dr["fEntityNo"].ToString();
                    t.rq_check = dr["rq_check"].ToString();
                    t.shiftno = dr["shiftno"].ToString();
                    t.bill_status = getIntString(dr, "bill_status");
                    t.fcount_mm = getDoubleString(dr, "fcount_mm");
                    t.generateflag = getIntString(dr, "generateflag");
                    t.totalpayable = getDoubleString(dr, "totalpayable");

                    DataRow datarow = dt.NewRow();
                    datarow["sync_id"] = t.sync_id;
                    datarow["club_id"] = 0;
                    datarow["sync_type"] = "0";
                    datarow["data_type"] = "pos_bills";
                    datarow["fdate"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    datarow["ftimestamp"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    datarow["fstatus"] = 1;
                    datarow["fjson"] = "[" + toJson(t) + "]";
                    datarow["fdemo"] = t.billcode;
                    datarow["SendCounts"] = 0;
                    datarow["LastSendTimes"] = null;
                    dt.Rows.Add(datarow);
                    i++;
                    if (i % jsonQty == 0)
                    {

                        SqlBulkCopyByDatatable(Sqlconn, "t_SyncFinancialRecords", dt);
                        dt.Clear();
                        listBox1.Items.Add("写入内存并且批量复制到表" + jsonQty + "条");

                    }
                    if ((a - i) < a % jsonQty)
                    {
                        //不满一万的剩余条数
                        plusQty++;
                        if (a - i == 0)
                        {
                            SqlBulkCopyByDatatable(Sqlconn, "t_SyncFinancialRecords", dt);
                            dt.Clear();
                            listBox1.Items.Add("写入内存并且批量复制到表" + plusQty + "条");
                        }
                    }
                    progressBar1.Value++;
                }
                DateTime dt2 = DateTime.Now;
                TimeSpan ts = dt2 - dt1;
                //progressBar1.Value = progressBar1.Value + a;
                listBox1.Items.Add("pos_bills完毕,总计：" + a + "条，完成:" + i + "条，差:" + (a - i) + "条，耗时" + ts.TotalSeconds.ToString());
                cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace, "操作失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString">目标连接字符</param>
        /// <param name="TableName">目标表</param>
        /// <param name="dt">源数据</param>
        private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dt);
                    }
                    catch (System.Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }
        public String toJson(object obj)
        {
            return JsonMapper.ToJson(obj);
        }
        private int SaveToTableSyncRecords(tongbu t)
        {
            int result = 0;
            DateTime now = DateTime.Now;
            string sql = @"insert into t_SyncFinancialRecords
                    (sync_id,club_id,sync_type,data_type,fdate,fstatus,fjson,fdemo)
                    values
                    ('" + t.sync_id + "',0,'" + t.sync_type + "','" + t.data_type + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "',1,'[" + toJson(t) + "]','0')";
            if (exec_sql(sql) > 0)
            {
                result = 1;
            }
            return result;
        }
        /// <summary>
        /// 如果不为空就转换为数字型，否则转换为空字符串
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public object getIntString(SqlDataReader reader, String fieldName)
        {
            if (reader[fieldName] != DBNull.Value)
            {
                if (reader[fieldName].ToString().Trim() != "")
                {
                    try
                    {
                        return Convert.ToInt32(reader[fieldName]); ;
                    }
                    catch
                    {
                        saveErrLog("int转换异常:" + fieldName + ":" + reader[fieldName].ToString() + "\n", "初始化错误");
                        return "";
                    }
                }
                else
                {
                    return "";
                }
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 如果不为空就转换为数字型，否则转换为空字符串
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public object getDoubleString(SqlDataReader reader, String fieldName)
        {
            if (reader[fieldName] != DBNull.Value)
            {
                if (reader[fieldName].ToString().Trim() != "")
                {
                    try
                    {
                        return Convert.ToDouble(reader[fieldName]);
                    }
                    catch
                    {
                        saveErrLog("double转换异常:" + fieldName + ":" + reader[fieldName].ToString() + "\n", "初始化错误");
                        return "";
                    }
                }
                else
                {
                    return "";
                }
            }
            else
            {
                return "";
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            pos_bills.RunWorkerAsync();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //progressBar1.Maximum = ExcuteScalarSQL("select ((select count(*) from pos_bills where ReadytoSync is null)+ (select count(*) from pos_sales where ReadytoSync is null)+(select count(*) from pos_bill_payment where ReadytoSync is null)+(select count(*) from fitness_checkin where ReadytoSync is null))");
            progressBar1.Maximum = ExcuteScalarSQL("select count(*) from pos_sales ");
            listBox1.Items.Add("统计待加工数据总条数：" + progressBar1.Maximum);
            pos_sales.RunWorkerAsync();
        }

        private void pos_sales_DoWork(object sender, DoWorkEventArgs e)
        {
            DateTime dt1 = DateTime.Now;
            try
            {
                a = ExcuteScalarSQL("select count(*) from pos_sales --where ReadytoSync is null");
                int i = 0;
                getsqlconn();
                SqlConnection cn = new SqlConnection(Sqlconn);
                cn.Open();
                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandText = "select saleid,billcode,grand_billcode,rec_status,sale_status,pay_status,sale_type,billshop,saleshop,payshop,produceshop,saledate,saletime,closedate,paydate,userid,itemcode,itemname1,itemname2,itemspec,saleprice,quantity,saletotal,discountpercent,discounttotal,gratuitypercent,gratuitytotal,rebatetotal,taxpercent,taxtotal,totalpayable,saleweight,stockprice,stockqty,modifier_memo,serverno,if_discount,if_gratuity,sale_prop,if_revenue,if_generate,if_other,if_invoice,closemethod,paymethod,checkin_id,orderno,seatno,printno,otherid,if_import,if_tax,pricemethod,sale_kinds,parent_kinds,sale_category,parent_category,sale_special,parent_special,stat_code,paymentid,fit_paymentid,payflag,itemid,autocdflag,otherflag,complimentary_flag,modifier,reason,kitchen_type,seat_no,is_meal,is_backup,meal_id,meal_type,is_inv,is_addtion,club_id,holidaymethod,clubcode,sttime,endtime,fcount_hh,fcount_mm,fcditemno,Fpcno,fclub,fEntityNo,fit_saleid,fcreatedate,fmodifydate,fUserNo,n_last,ifdown,xh,dzxh,dj_cb,je_cb,bm_ck,is_check,walkcust_ftype,keyno,memno,Fremark,deposit,fcusttdno,fbillNosub,disccode,spandhyacc,ch_printflag,accuserid,fdayandhousr,fmpdaycount,zbcardno,v_artno,Fmp_m_id,acc_clubkinds1,acc_clubkinds2,acc_clubkinds3 from pos_sales --where ReadytoSync is null";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    pos_sales t = new pos_sales();

                    t.sync_id = Guid.NewGuid().ToString("D");
                    t.LocalId = localid;
                    t.club_id = Convert.ToInt64(remoteid);
                    t.data_type = "pos_sales";
                    t.sync_type = 0;
                    //生成代码

                    t.fUserNo = dr["fUserNo"].ToString();
                    t.v_artno = dr["v_artno"].ToString();
                    t.reason = dr["reason"].ToString();
                    t.saleprice = getDoubleString(dr, "saleprice");
                    t.if_gratuity = getIntString(dr, "if_gratuity");
                    t.fcount_mm = getDoubleString(dr, "fcount_mm");
                    t.walkcust_ftype = getIntString(dr, "walkcust_ftype");
                    t.taxpercent = getDoubleString(dr, "taxpercent");
                    //t.club_id = dr["club_id"].ToString();
                    t.if_tax = getIntString(dr, "if_tax");
                    t.saleid = getDoubleString(dr, "saleid");
                    t.paydate = dr["paydate"].ToString();
                    t.sale_special = dr["sale_special"].ToString();
                    t.saleshop = dr["saleshop"].ToString();
                    t.itemname1 = dr["itemname1"].ToString();
                    t.if_generate = getIntString(dr, "if_generate");
                    t.acc_clubkinds2 = dr["acc_clubkinds2"].ToString();
                    t.xh = getIntString(dr, "xh");
                    t.is_meal = dr["is_meal"].ToString();
                    t.sttime = dr["sttime"].ToString();
                    t.modifier_memo = dr["modifier_memo"].ToString();
                    t.parent_kinds = dr["parent_kinds"].ToString();
                    t.saleweight = getDoubleString(dr, "saleweight");
                    t.Fremark = dr["Fremark"].ToString();
                    t.meal_type = dr["meal_type"].ToString();
                    t.je_cb = getDoubleString(dr, "je_cb");
                    t.printno = getIntString(dr, "printno");
                    t.paymentid = getIntString(dr, "paymentid");
                    t.fdayandhousr = getDoubleString(dr, "fdayandhousr");
                    t.saledate = dr["saledate"].ToString();
                    t.itemcode = dr["itemcode"].ToString();
                    t.acc_clubkinds1 = dr["acc_clubkinds1"].ToString();
                    t.checkin_id = dr["checkin_id"].ToString();
                    t.saletotal = getDoubleString(dr, "saletotal");
                    t.if_revenue = getIntString(dr, "if_revenue");
                    t.stockqty = getDoubleString(dr, "stockqty");
                    t.Fpcno = dr["Fpcno"].ToString();
                    t.clubcode = dr["clubcode"].ToString();
                    t.memno = dr["memno"].ToString();
                    t.sale_kinds = dr["sale_kinds"].ToString();
                    t.dj_cb = getDoubleString(dr, "dj_cb");
                    t.grand_billcode = dr["grand_billcode"].ToString();
                    t.spandhyacc = dr["spandhyacc"].ToString();
                    t.stat_code = dr["stat_code"].ToString();
                    t.if_invoice = getIntString(dr, "if_invoice");
                    t.itemspec = dr["itemspec"].ToString();
                    t.fit_saleid = dr["fit_saleid"].ToString();
                    t.otherflag = getIntString(dr, "otherflag");
                    t.if_discount = getIntString(dr, "if_discount");
                    t.gratuitypercent = getDoubleString(dr, "gratuitypercent");
                    t.fcount_hh = getDoubleString(dr, "fcount_hh");
                    t.pay_status = getIntString(dr, "pay_status");
                    t.is_addtion = dr["is_addtion"].ToString();
                    t.is_check = getIntString(dr, "is_check");
                    t.closedate = dr["closedate"].ToString();
                    t.payflag = getIntString(dr, "payflag");
                    t.fcusttdno = dr["fcusttdno"].ToString();
                    t.parent_category = dr["parent_category"].ToString();
                    t.itemname2 = dr["itemname2"].ToString();
                    t.if_other = getIntString(dr, "if_other");
                    t.autocdflag = getIntString(dr, "autocdflag");
                    t.ifdown = dr["ifdown"].ToString();
                    t.seatno = dr["seatno"].ToString();
                    t.seat_no = getIntString(dr, "seat_no");
                    t.discounttotal = getDoubleString(dr, "discounttotal");
                    t.endtime = dr["endtime"].ToString();
                    t.fEntityNo = dr["fEntityNo"].ToString();
                    t.totalpayable = getDoubleString(dr, "totalpayable");
                    t.fit_paymentid = dr["fit_paymentid"].ToString();
                    t.sale_status = getIntString(dr, "sale_status");
                    t.produceshop = dr["produceshop"].ToString();
                    t.deposit = getDoubleString(dr, "deposit");
                    t.accuserid = dr["accuserid"].ToString();
                    t.fmodifydate = dr["fmodifydate"].ToString();
                    t.modifier = dr["modifier"].ToString();
                    t.zbcardno = dr["zbcardno"].ToString();
                    t.paymethod = dr["paymethod"].ToString();
                    t.quantity = getDoubleString(dr, "quantity");
                    t.fcditemno = dr["fcditemno"].ToString();
                    t.meal_id = getIntString(dr, "meal_id");
                    t.sale_prop = getIntString(dr, "sale_prop");
                    t.rebatetotal = getDoubleString(dr, "rebatetotal");
                    t.billshop = dr["billshop"].ToString();
                    t.keyno = dr["keyno"].ToString();
                    t.billcode = dr["billcode"].ToString();
                    t.if_import = getIntString(dr, "if_import");
                    t.disccode = dr["disccode"].ToString();
                    t.parent_special = dr["parent_special"].ToString();
                    t.complimentary_flag = dr["complimentary_flag"].ToString();
                    t.closemethod = dr["closemethod"].ToString();
                    t.fmpdaycount = getIntString(dr, "fmpdaycount");
                    t.fcreatedate = dr["fcreatedate"].ToString();
                    t.acc_clubkinds3 = dr["acc_clubkinds3"].ToString();
                    t.dzxh = getDoubleString(dr, "dzxh");
                    t.is_backup = dr["is_backup"].ToString();
                    t.gratuitytotal = getDoubleString(dr, "gratuitytotal");
                    t.serverno = dr["serverno"].ToString();
                    t.sale_type = getIntString(dr, "sale_type");
                    t.sale_category = dr["sale_category"].ToString();
                    t.is_inv = dr["is_inv"].ToString();
                    t.bm_ck = dr["bm_ck"].ToString();
                    t.otherid = getIntString(dr, "otherid");
                    t.saletime = dr["saletime"].ToString();
                    t.itemid = getIntString(dr, "itemid");
                    t.fbillNosub = dr["fbillNosub"].ToString();
                    t.Fmp_m_id = dr["Fmp_m_id"].ToString();
                    t.kitchen_type = dr["kitchen_type"].ToString();
                    t.n_last = getDoubleString(dr, "n_last");
                    t.discountpercent = getDoubleString(dr, "discountpercent");
                    t.orderno = getIntString(dr, "orderno");
                    t.fclub = dr["fclub"].ToString();
                    t.stockprice = getDoubleString(dr, "stockprice");
                    t.holidaymethod = getIntString(dr, "holidaymethod");
                    t.taxtotal = getDoubleString(dr, "taxtotal");
                    t.pricemethod = getIntString(dr, "pricemethod");
                    t.rec_status = getIntString(dr, "rec_status");
                    t.userid = dr["userid"].ToString();
                    t.ch_printflag = getIntString(dr, "ch_printflag");
                    t.payshop = dr["payshop"].ToString();

                    DataRow datarow = dt.NewRow();
                    datarow["sync_id"] = t.sync_id;
                    datarow["club_id"] = 0;
                    datarow["sync_type"] = "0";
                    datarow["data_type"] = "pos_sales";
                    datarow["fdate"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    datarow["ftimestamp"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    datarow["fstatus"] = 1;
                    datarow["fjson"] = "[" + toJson(t) + "]";
                    datarow["fdemo"] = t.billcode;
                    datarow["SendCounts"] = 0;
                    datarow["LastSendTimes"] = null;
                    dt.Rows.Add(datarow);
                    i++;
                    if (i % jsonQty == 0)
                    {

                        SqlBulkCopyByDatatable(Sqlconn, "t_SyncFinancialRecords", dt);
                        dt.Clear();
                        listBox1.Items.Add("写入内存并且批量复制到表" + jsonQty + "条");

                    }
                    if ((a - i) < a % jsonQty)
                    {
                        //不满一万的剩余条数
                        plusQty++;
                        if (a - i == 0)
                        {
                            SqlBulkCopyByDatatable(Sqlconn, "t_SyncFinancialRecords", dt);
                            dt.Clear();
                            listBox1.Items.Add("写入内存并且批量复制到表" + plusQty + "条");
                        }
                    }
                    progressBar1.Value++;
                }
                DateTime dt2 = DateTime.Now;
                TimeSpan ts = dt2 - dt1;
                //progressBar1.Value = progressBar1.Value + a;
                listBox1.Items.Add("pos_sales完毕,总计：" + a + "条，完成:" + i + "条，差:" + (a - i) + "条，耗时" + ts.TotalSeconds.ToString());
                cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace, "操作失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void pos_bills_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //pos_sales.RunWorkerAsync();
        }

        private void pos_bill_payment_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                a = ExcuteScalarSQL("select count(*) from pos_bill_payment where ReadytoSync is null");
                int i = 0;
                getsqlconn();
                SqlConnection cn = new SqlConnection(Sqlconn);
                cn.Open();
                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandText = "select paymentid,billcode,rec_status,pay_status,paymethod,payparent,payamount,payforeign,payshop,paydate,payflag,payrate,payfamt,paychanges,paycardno,ExpiryDate,paysysid,payname,userid,shiftno,if_invoice,if_revenue,payno,paymemo,paytype,memid,clubcode,fit_paymentid,fEntityNo,FClub,Fpcno,fcreatedate,fmodifydate,fUserNo,n_last,ifdown,syscode,paycode,sale_kinds,sale_category,sale_special,fifrepacc,fifdhyhpay from pos_bill_payment where ReadytoSync is null";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    pos_bill_payment t = new pos_bill_payment();

                    t.sync_id = Guid.NewGuid().ToString("D");
                    t.LocalId = localid;
                    t.club_id = Convert.ToInt64(remoteid);
                    t.data_type = "pos_bill_payment";
                    t.sync_type = 0;

                    t.n_last = getDoubleString(dr, "n_last");
                    t.sale_special = dr["sale_special"].ToString();
                    t.payparent = dr["payparent"].ToString();
                    t.payname = dr["payname"].ToString();
                    t.payrate = getDoubleString(dr, "payrate");
                    t.paycode = dr["paycode"].ToString();
                    t.paycardno = dr["paycardno"].ToString();
                    t.fcreatedate = dr["fcreatedate"].ToString();
                    t.fEntityNo = dr["fEntityNo"].ToString();
                    t.if_invoice = getIntString(dr, "if_invoice");
                    t.payshop = dr["payshop"].ToString();
                    t.paychanges = getDoubleString(dr, "paychanges");
                    t.fifdhyhpay = getIntString(dr, "fifdhyhpay");
                    t.syscode = dr["syscode"].ToString();
                    t.memid = dr["memid"].ToString();
                    t.shiftno = dr["shiftno"].ToString();
                    t.fUserNo = dr["fUserNo"].ToString();
                    t.sale_category = dr["sale_category"].ToString();
                    t.rec_status = getIntString(dr, "rec_status");
                    t.payno = dr["payno"].ToString();
                    t.payflag = getIntString(dr, "payflag");
                    t.paysysid = getIntString(dr, "paysysid");
                    t.sale_kinds = dr["sale_kinds"].ToString();
                    t.Fpcno = dr["Fpcno"].ToString();
                    t.billcode = dr["billcode"].ToString();
                    t.fit_paymentid = dr["fit_paymentid"].ToString();
                    t.if_revenue = getIntString(dr, "if_revenue");
                    t.payforeign = dr["payforeign"].ToString();
                    t.fifrepacc = getIntString(dr, "fifrepacc");
                    t.paymethod = dr["paymethod"].ToString();
                    t.paytype = dr["paytype"].ToString();
                    t.userid = dr["userid"].ToString();
                    t.ExpiryDate = dr["ExpiryDate"].ToString();
                    t.fmodifydate = dr["fmodifydate"].ToString();
                    t.pay_status = getIntString(dr, "pay_status");
                    t.paymemo = dr["paymemo"].ToString();
                    t.paydate = dr["paydate"].ToString();
                    t.FClub = dr["FClub"].ToString();
                    t.ifdown = dr["ifdown"].ToString();
                    t.clubcode = dr["clubcode"].ToString();
                    t.paymentid = getDoubleString(dr, "paymentid");
                    t.payamount = getDoubleString(dr, "payamount");
                    t.payfamt = getDoubleString(dr, "payfamt");
                    SaveToTableSyncRecords(t);
                    exec_sql("update pos_bill_payment set ReadytoSync=1 where paymentid='" + t.paymentid + "'");
                    i++;
                    progressBar1.Value++;
                }
                listBox1.Items.Add("pos_bill_payment完毕,总计：" + a + "条，完成:" + i + "条，差:" + (a - i) + "条");
                cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace, "操作失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void fitness_checkin_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                a = ExcuteScalarSQL("select count(*) from fitness_checkin where ReadytoSync is null");
                int i = 0;
                getsqlconn();
                SqlConnection cn = new SqlConnection(Sqlconn);
                cn.Open();
                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandText = "select sid,cardno,cname,sex,mem_status,mem_type,checkin_date,checkin_time,operator,checkin_status,if_towel,if_keyring,keyno,if_returnkey,checkouttime,memo,color,memiden,memid,workshop,expendcard,deposit,guesttype,issuecard,lockstatus,clubcode,posflag,fEntityNo,FClub,Fpcno,fcreatedate,fmodifydate,fUserNo,n_last,ifdown,fmp,ifinshoes,ifshoes,shoesdate1,fbillNo,fmpdate,shoesdate2,ifoutshoes,fgphzs,fbphzs,fsalempdttime,ifcusttype,fje,fqry,fcusttdno,fbillNosub,city_id,fmpsttdate,fmpenddate,fmpusermin,fmpdaycount,shoesdate4,fmpjeall,fyjjeall,finje,ftime_mode from fitness_checkin where ReadytoSync is null";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    fitness_checkin t = new fitness_checkin();

                    t.sync_id = Guid.NewGuid().ToString("D");
                    t.LocalId = localid;
                    t.club_id = Convert.ToInt64(remoteid);
                    t.data_type = "fitness_checkin";
                    t.sync_type = 0;

                    t.fje = getDoubleString(dr, "fje");
                    t.lockstatus = getIntString(dr, "lockstatus");
                    t.cardno = dr["cardno"].ToString();
                    t.shoesdate2 = dr["shoesdate2"].ToString();
                    t.fyjjeall = getDoubleString(dr, "fyjjeall");
                    t.fmp = dr["fmp"].ToString();
                    t.memo = dr["memo"].ToString();
                    t.deposit = getDoubleString(dr, "deposit");
                    t.fbphzs = getIntString(dr, "fbphzs");
                    t.fEntityNo = dr["fEntityNo"].ToString();
                    t.shoesdate1 = dr["shoesdate1"].ToString();
                    t.fmpdaycount = getIntString(dr, "fmpdaycount");
                    t.mem_status = getIntString(dr, "mem_status");
                    t.memid = dr["memid"].ToString();
                    t.keyno = dr["keyno"].ToString();
                    t.fcreatedate = dr["fcreatedate"].ToString();
                    t.fbillNo = dr["fbillNo"].ToString();
                    t.n_last = getDoubleString(dr, "n_last");
                    t.shoesdate4 = dr["shoesdate4"].ToString();
                    t.checkin_time = dr["checkin_time"].ToString();
                    t.workshop = dr["workshop"].ToString();
                    t.if_returnkey = getIntString(dr, "if_returnkey");
                    t.fbillNosub = dr["fbillNosub"].ToString();
                    t.ifoutshoes = dr["ifoutshoes"].ToString();
                    t.ifinshoes = dr["ifinshoes"].ToString();
                    t.if_towel = getIntString(dr, "if_towel");
                    t.finje = getDoubleString(dr, "finje");
                    t.color = getIntString(dr, "color");
                    t.fmpenddate = dr["fmpenddate"].ToString();
                    t.guesttype = getIntString(dr, "guesttype");
                    t.fsalempdttime = dr["fsalempdttime"].ToString();
                    t.city_id = dr["city_id"].ToString();
                    t.FClub = dr["FClub"].ToString();
                    t.ifshoes = dr["ifshoes"].ToString();
                    t.if_keyring = getIntString(dr, "if_keyring");
                    t.mem_type = getIntString(dr, "mem_type");
                    t.fmpusermin = getIntString(dr, "fmpusermin");
                    t.cname = dr["cname"].ToString();
                    t.fqry = getDoubleString(dr, "fqry");
                    t.clubcode = dr["clubcode"].ToString();
                    t.fmodifydate = dr["fmodifydate"].ToString();
                    t.ifdown = dr["ifdown"].ToString();
                    t.fmpjeall = getDoubleString(dr, "fmpjeall");
                    t.checkouttime = dr["checkouttime"].ToString();
                    t.@operator = dr["operator"].ToString();
                    t.posflag = getIntString(dr, "posflag");
                    t.expendcard = dr["expendcard"].ToString();
                    t.sex = dr["sex"].ToString();
                    t.fcusttdno = dr["fcusttdno"].ToString();
                    t.fgphzs = getIntString(dr, "fgphzs");
                    t.fUserNo = dr["fUserNo"].ToString();
                    t.memiden = dr["memiden"].ToString();
                    t.fmpsttdate = dr["fmpsttdate"].ToString();
                    t.checkin_status = getIntString(dr, "checkin_status");
                    t.ftime_mode = dr["ftime_mode"].ToString();
                    t.issuecard = getIntString(dr, "issuecard");
                    t.ifcusttype = getIntString(dr, "ifcusttype");
                    t.sid = dr["sid"].ToString();
                    t.Fpcno = dr["Fpcno"].ToString();
                    t.fmpdate = dr["fmpdate"].ToString();
                    t.checkin_date = dr["checkin_date"].ToString();


                    SaveToTableSyncRecords(t);
                    exec_sql("update fitness_checkin set ReadytoSync=1 where sid='" + t.sid + "'");
                    i++;
                    progressBar1.Value++;
                }
                listBox1.Items.Add("fitness_checkin完毕,总计：" + a + "条，完成:" + i + "条，差:" + (a - i) + "条");
                cn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace, "操作失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void pos_sales_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            pos_bill_payment.RunWorkerAsync();
        }

        private void pos_bill_payment_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            fitness_checkin.RunWorkerAsync();
        }

        private void listBox1_MouseDoubleClick(object sender, MouseEventArgs e)
        {

            MessageBox.Show(listBox1.SelectedItem.ToString());
        }

        private void button5_Click(object sender, EventArgs e)
        {
            pos_sales.RunWorkerAsync();
        }

        //将转为字典，然后调用服务器的同种方法把字典转为sql
        private string GetOneRowSql(DataSet schema, DataRow row, string tableName)
        {

            Dictionary<string, Object> obj = new Dictionary<string, object>();
            obj.Add("data_type", tableName);
            foreach (DataColumn col in schema.Tables[0].Columns)
            {
                obj.Add(col.ColumnName, row[col.ColumnName]);
            }
            if (obj["data_type"].ToString() == "sys_workshop")
            {
                //过滤此表的字段
                FilterString(obj, null, "SHOP_CODE", null);

            }
            if (obj["data_type"].ToString() == "sys_user")
            {
                //过滤此表的字段
                FilterString(obj, "empid", "USER_ID,WORKSHOP_ID", null);
            }
            if (obj["data_type"].ToString() == "sys_payment")
            {
                //过滤此表的字段
                FilterString(obj, "detail_type", null, "code");

            }
            if (obj["data_type"].ToString() == "syskindacc")
            {
                //过滤此表的字段
                FilterString(obj, null, null, "code");

            }
            if (obj["data_type"].ToString() == "fitness_checkin")
            {
                //过滤此表的字段
                FilterString(obj, "sid,operator,mem_type,memid", "cardno,memiden,workshop,fbillNo", null);

            }
            if (obj["data_type"].ToString() == "pos_bill_payment")
            {
                //过滤此表的字段
                FilterString(obj, "paymentid,memid,userid", "billcode,payshop", "paymethod,payparent,paycode");

            }
            if (obj["data_type"].ToString() == "pos_sales")
            {
                //过滤此表的字段
                FilterString(obj, "saleid,userid", "billcode,billshop,grand_billcode,saleshop,payshop,memno", "closemethod,paymethod,sale_kinds,parent_kinds,sale_category,parent_category");

            }
            if (obj["data_type"].ToString() == "pos_bills")
            {
                //过滤此表的字段
                FilterString(obj, "openuserid,closeuserid,memsid,accuserid,memid", "billcode,billshop,grand_billcode,saleshop,payshop,memno,guest_identity", "paymethod,paycode");

            }
            if (obj["data_type"].ToString() == "fitness_cardchange")
            {
                //过滤此表的字段
                FilterString(obj, "sid,memkind,newmemkind,usercode,memid,sale_name", "cardno,newcardno", null);

            }
            if (obj["data_type"].ToString() == "jxc_bm_ck")
            {

            }
            if (obj["data_type"].ToString() == "memcardlog")
            {
                //过滤此表的字段
                FilterString(obj, "sid,memid,usercode,timeitem,sale_name", "cardno,workshop", null);
            }
            if (obj["data_type"].ToString() == "mem_member")
            {
                //过滤此表的字段
                FilterString(obj, "q_id,user_id,memberkind,sale_name,modify", "m_id,tickh,mem_iden,main_id,houseid,billtocard", null);

            }
            if (obj["data_type"].ToString() == "fitness_enroll_qchange")
            {
                //过滤此表的字段
                FilterString(obj, "sid,operator", "class,cardno,foldclass", null);

            }
            if (obj["data_type"].ToString() == "sys_identity")
            {
                //过滤此表的字段
                FilterString(obj, null, "code", null);
            }
            if (obj["data_type"].ToString() == "fitness_classes")
            {
                //过滤此表的字段
                FilterString(obj, "instructor", "code", null);
            }
            if (obj["data_type"].ToString() == "mem_kind")
            {
                //过滤此表的字段
                FilterString(obj, "memberkind", "icno", null);
            }
            if (obj["data_type"].ToString() == "SnArt")
            {
                //过滤此表的字段
                FilterString(obj, "v_artNo", null, null);
            }
            if (obj["data_type"].ToString() == "gym_reserve")
            {
                //过滤此表的字段
                FilterString(obj, "sid,time_mode,period_id,res_item,operator,memid", "cardno,dcbillcode", null);
            }
            if (obj["data_type"].ToString() == "fitness_enroll")
            {
                //过滤此表的字段
                FilterString(obj, "sid,operator,memid,fartjlno,sale_name", "class,cardno", null);
            }
            if (obj["data_type"].ToString() == "gym_period")
            {
                //过滤此表的字段
                FilterString(obj, "sid,time_mode", null, null);

            }
            if (obj["data_type"].ToString() == "t_fsl_sk_student05")
            {
                //过滤此表的字段
                FilterString(obj, "fpartno,fbbjlman", "v_bcname,fbillNo,kh", null);
            }
            if (obj["data_type"].ToString() == "gym_item")
            {
                //过滤此表的字段
                FilterString(obj, "item_code,time_mode", "fbillNo", null);

            }
            if (obj["data_type"].ToString() == "fitness_monthfee")
            {
                //过滤此表的字段
                FilterString(obj, "sid,usercode,memid,sale_name", "cardno,billcode", null);
            }
            if (obj["data_type"].ToString() == "gym_kind")
            {
                //过滤此表的字段
                FilterString(obj, "code", "shop_code", null);

            }
            if (obj["data_type"].ToString() == "jxc_dj_kc_2")
            {

            }
            if (obj["data_type"].ToString() == "jxc_dj_kc_zc_2")
            {

            }
            if (obj["data_type"].ToString() == "jxc_dj_kc_db_2")
            {

            }
            if (obj["data_type"].ToString() == "jxc_dj_xs_2")
            {

            }
            if (obj["data_type"].ToString() == "jxc_dj_gh_2")
            {

            }
            if (obj["data_type"].ToString() == "jxc_bm_sp")
            {

            }
            if (obj["data_type"].ToString() == "jxc_zb_sp_kc")
            {

            }
            if (obj["data_type"].ToString() == "gym_status")
            {
                //过滤此表的字段
                FilterString(obj, "period_id,item_code,time_mode", "sid,operator", null);
            }
            if (obj["data_type"].ToString() == "card_times_info")
            {
                //过滤此表的字段
                FilterString(obj, "timescode", null, null);
            }
            if (obj["data_type"].ToString() == "t_fsl_Entity1")
            {
                //过滤此表的字段
                //FilterString(obj, null, "code", null);
            }
            if (obj["data_type"].ToString() == "t_fsl_Entity2")
            {
                //过滤此表的字段
                //FilterString(obj, null, "code,parentcode", null);
            }
            return DictToInsertSQL(obj);
        }

        #region //原GetOneRowSql方法
        //private string GetOneRowSql(DataSet schema, DataRow row, string tableName)
        //{
        //    StringBuilder insertString = new StringBuilder();

        //    insertString.AppendFormat("Insert {0} (", tableName);

        //    int i = 0;

        //    foreach (DataColumn col in schema.Tables[0].Columns)
        //    {
        //        if (i == 0)
        //        {
        //            insertString.AppendFormat("[{0}]", col.ColumnName);
        //        }
        //        else
        //        {
        //            insertString.AppendFormat(", [{0}]", col.ColumnName);
        //        }

        //        i++;

        //    }

        //    insertString.Append(") Values(");

        //    i = 0;

        //    foreach (DataColumn col in schema.Tables[0].Columns)
        //    {
        //        string value;
        //        if (col.ColumnName.ToString() == "Fclub" || col.ColumnName.ToString() == "FClub" || col.ColumnName.ToString() == "fclub")
        //        {
        //            value = "'" + txtFclub.Text.Trim() + "'";
        //            //如果是分公司编号的列直接赋值
        //            goto jump;
        //        }

        //        if (row[col.ColumnName] == DBNull.Value)
        //        {
        //            value = "NULL";
        //        }
        //        else
        //        {

        //            if (col.DataType == typeof(string))
        //            {

        //                value = "'" + row[col.ColumnName].ToString().Replace("'", "''") + FilterString(col.ColumnName.ToString(), tableName) + "'";
        //            }
        //            else if (col.DataType == typeof(DateTime))
        //            {
        //                value = "'" + ((DateTime)(row[col.ColumnName])).ToString("yyyy-MM-dd HH:mm:ss") + "'";
        //            }
        //            else
        //            {
        //                value = row[col.ColumnName].ToString() + FilterString(col.ColumnName.ToString(), tableName);
        //            }
        //        }

        //    //跳过if判断
        //    jump:

        //        if (i == 0)
        //        {
        //            insertString.AppendFormat("{0}", value);
        //        }
        //        else
        //        {
        //            insertString.AppendFormat(", {0}", value);
        //        }

        //        i++;
        //    }

        //    insertString.Append(");");

        //    return insertString.ToString();
        //} 
        #endregion

        //财务参数指定的字段值不加分公司
        //public string[] kindaccCodeArr = { "$54", "$56", "$74", "$76", "$77", "$80", "$82", "$85", "$86", "$88", "$90", "$99", "$91", "$93", "$95", "$97", "54", "56", "74", "76", "77", "80", "82", "85", "86", "88", "90", "91", "93", "95", "97", "99" };
        public ArrayList kindaccCodeArr = null;
        //付款方式指定的字段值不加分公司
        //public string[] paymentCodeArr = { "0001", "0002", "0003", "0004", "0006", "0007", "0008", "1001", "1003", "2001", "2002", "2003", "3001", "6999", "7999" };
        public ArrayList paymentCodeArr = null;

        //获取不需要加默认规则的字段值
        public void getFilteringRules()
        {

            //实例化
            kindaccCodeArr = new ArrayList();
            paymentCodeArr = new ArrayList();

            if (!Directory.Exists(Application.StartupPath + "\\过滤规则"))
            {
                //如果不存在过滤规则文件夹就用此默认规则

                string[] kindaccCode = { "$54", "$56", "$74", "$76", "$77", "$80", "$82", "$85", "$86", "$88", "$90", "$99", "$91", "$93", "$95", "$97", "54", "56", "74", "76", "77", "80", "82", "85", "86", "88", "90", "91", "93", "95", "97", "99" };
                string[] paymentCode = { "0001", "0002", "0003", "0004", "0006", "0007", "0008", "1001", "1003", "2001", "2002", "2003", "3001", "6999", "7999" };

                for (int i = 0; i < kindaccCode.Length; i++)
                {
                    kindaccCodeArr.Add(kindaccCode[i]);
                }

                for (int i = 0; i < paymentCode.Length; i++)
                {
                    paymentCodeArr.Add(paymentCode[i]);
                }
                return;
            }

            //获取的路径
            string[] path = { Application.StartupPath + @"\过滤规则\sys_payment.txt", Application.StartupPath + @"\过滤规则\syskindacc.txt" };

            Thread worker = new Thread(delegate()
            {
                for (int i = 0; i < path.Length; i++)
                {
                    if (File.Exists(path[i]))
                    {
                        using (StreamReader sr = new StreamReader(path[i]))
                        {

                            string line = string.Empty;
                            while ((line = sr.ReadLine()) != null)
                            {
                                if (line.Trim() != "")
                                {
                                    if (path[i].Contains("sys_payment"))
                                        paymentCodeArr.Add(line.Trim());
                                    else
                                        kindaccCodeArr.Add(line.Trim());
                                }
                            }
                        }

                    }
                }
            }
               );
            worker.IsBackground = true;
            worker.Start();

        }

        //给指定的列的值加后缀避免重复
        private void FilterString(Dictionary<string, Object> obj, string fclubField, string letterField, string judgmentField)
        {
            //判断是否勾选了禁用规则
            if (checkBox36.Checked)
            {
                return;
            }
            //替换分公司
            if (obj.ContainsKey("fclub"))
            {
                obj["fclub"] = txtFclub.Text;
            }
            if (obj.ContainsKey("Fclub"))
            {
                obj["Fclub"] = txtFclub.Text;
            }
            if (obj.ContainsKey("FClub"))
            {
                obj["FClub"] = txtFclub.Text;
            }
            if (obj.ContainsKey("clubcode"))
            {
                obj["clubcode"] = txtFclub.Text;
            }
            //需要加分公司编号的列
            if (fclubField != null)
            {
                string[] field = fclubField.Split(',');
                //遍历列数组
                foreach (string item in field)
                {
                    //判断字典是否存在&&是否包含不为空的列值
                    if (obj.ContainsKey(item) && obj[item].ToString().Length != 0)
                    {
                        if (obj[item].GetType() == typeof(string))
                        {
                            //如果是加上分公司编号后缀
                            obj[item] += txtFclub.Text;
                        }
                        //MessageBox.Show(obj[item].GetType().ToString());
                        if (obj[item].GetType() == typeof(Int32) || obj[item].GetType() == typeof(Double) || obj[item].GetType() == typeof(Decimal))
                        {
                            if (Convert.ToDouble(obj[item]) != 0)
                                obj[item] = Convert.ToInt64(obj[item] + txtFclub.Text);
                        }
                    }
                }
            }

            //需要加指定字母的列
            if (letterField != null)
            {
                string[] field = letterField.Split(',');
                foreach (string item in field)
                {
                    //判断字典是否存在&&是否包含不为空的列值
                    if (obj.ContainsKey(item) && obj[item].ToString().Length != 0)
                    {
                        //将该列的值加上指定字母后缀
                        obj[item] = obj[item].ToString().Trim() + txtString.Text;
                    }
                }
            }
            //需要判断值列的值后，加分公司编号
            if (judgmentField != null)
            {
                string[] field = judgmentField.Split(',');
                foreach (string item in field)
                {
                    if (obj.ContainsKey(item))
                    {
                        //如果和付款方式的列有关
                        if (item == "paymethod" || item == "closemethod" || item == "payparent" || (item == "code" && obj["data_type"].ToString() == "sys_payment"))
                        {
                            //判断不在系统默认列值内的和不为空的加上分公司后缀
                            if (!paymentCodeArr.Contains(obj[item].ToString()) && obj[item].ToString().Length != 0)
                            {
                                obj[item] += txtFclub.Text;
                            }
                        }
                        else
                        {
                            if (!kindaccCodeArr.Contains(obj[item].ToString()) && obj[item].ToString().Length != 0)
                            {
                                obj[item] += txtFclub.Text;
                            }
                        }
                    }
                }
            }
        }


        public static string toNull(Dictionary<string, Object> dict, string key)
        {
            string returnValue = dict[key].ToString();
            if (dict[key].ToString().Length == 0)
            {
                return "null";
            }
            if (dict[key].GetType() == typeof(string))
            {
                returnValue = string.Format("'{0}'", dict[key].ToString().Replace("'", ""));//有的值有'导致生成的sql语法不规范
            }
            else if (dict[key].GetType() == typeof(DateTime))
            {
                returnValue = string.Format("'{0}'", dict[key]);
            }
            return returnValue;
        }
        //是否包含了默认的key
        //如果是返回true
        //遇到contine跳过当前循环
        public static bool ifHasDefaultKeys(string key)
        {
            return (key == "LocalId" || key == "sync_type" || key == "data_type" || key == "sync_id" || key == "timestamp" || key == "new_timestamp");
        }
        /// <summary>
        /// 根据字典生成sql插入语句
        /// </summary>
        /// <param name="dict"></param>
        /// <returns>sql插入语句</returns>
        public static string DictToInsertSQL(Dictionary<string, Object> dict)
        {
            StringBuilder sqlText = new StringBuilder();
            //sqlText.AppendLine("SET IDENTITY_INSERT  " + dict["data_type"] + " on ");
            sqlText.AppendFormat("Insert {0} (", dict["data_type"]);
            int i = 0;
            foreach (string key in dict.Keys)
            {
                if (ifHasDefaultKeys(key))
                    continue;
                if (i == 0)
                {
                    sqlText.AppendFormat("[{0}]", key.ToString());
                }
                else
                {
                    sqlText.AppendFormat(", [{0}]", key.ToString());
                }
                i++;
            }
            sqlText.Append(") Values(");
            i = 0;
            foreach (string key in dict.Keys)
            {

                if (ifHasDefaultKeys(key))
                    continue;
                if (i == 0)
                {

                    sqlText.AppendFormat("{0}", toNull(dict, key));
                }
                else
                {
                    sqlText.AppendFormat(",{0}", toNull(dict, key));
                }

                i++;
            }
            sqlText.Append(");");
            //sqlText.AppendLine("SET IDENTITY_INSERT  " + dict["data_type"] + " off ");
            return sqlText.ToString();
        }

        #region 原过滤过滤规则
        //给指定的列的值加后缀避免重复
        //private string FilterString(string field, string tableName)
        //{
        //    string value = string.Empty;
        //    //字符串型的主键在后面加指定的字符串
        //    if (field == "billcode" || field == "grand_billcode" || field == "fit_paymentid" ||
        //        field == "fit_saleid" || field == "fbillno" || field == "fbillnosub" || field == "USER_ID" || field == "SHOP_CODE" ||
        //        field == "billshop" || field == "saleshop" || field == "payshop")
        //    {
        //        value += txtString.Text;
        //    }
        //    //数字型的主键在后面加分公司编号
        //    if (field == "saleid" || field == "paymethod" || field == "paycode" || field == "sale_kinds" || field == "parent_kinds" ||
        //        field == "sale_category" || field == "parent_category" || field == "sid" || field == "syscode" ||
        //        field == "code" || field == "closemethod" || field == "closemethod" || field == "paymentid" || field == "payparent" ||
        //        field == "parent_code" || field == "empid" || field == "userid" || field == "openuserid" || field == "closeuserid" ||
        //        field == "modify" || field == "accuserid" || field == "operator")
        //    {
        //        value += txtFclub.Text;
        //    }

        //    //选择性加分公司编号
        //    //if (tableName==)
        //    return value;
        //} 
        #endregion

        #region 给richTextbox追加文本
        public void SelectNextTabPage()
        {

            Action<string> actionDelegate = (x) =>
            {
                tabControl2.SelectTab(tabControl2.SelectedIndex + 1);
            };
            this.tabControl2.Invoke(actionDelegate, "");

        }
        #endregion

        //生成多少条sql
        //private string topCount = " top 1000 * ";
        private string topCount = " * ";
        private void button9_Click(object sender, EventArgs e)
        {

            Thread worker = new Thread(delegate()
               {

                   createSQLScript("select " + topCount + " from pos_bills", "pos_bills", textBox8, checkBox9.Checked);
                   createSQLScript("select " + topCount + " from pos_sales", "pos_sales", textBox7, checkBox10.Checked);
                   createSQLScript("select  " + topCount + " from pos_bill_payment", "pos_bill_payment", textBox6, checkBox3.Checked);
                   createSQLScript("select  " + topCount + " from fitness_checkin", "fitness_checkin", textBox1, checkBox4.Checked);
                   createSQLScript("select " + topCount + " from syskindacc", "syskindacc", textBox9, checkBox4.Checked);
                   createSQLScript("select " + topCount + " from sys_payment", "sys_payment", textBox10, checkBox6.Checked);
                   createSQLScript("select " + topCount + " from sys_user", "sys_user", textBox13, checkBox7.Checked);
                   createSQLScript("select " + topCount + " from sys_workshop", "sys_workshop", textBox14, checkBox8.Checked);
                   if (checkBox11.Checked == true)
                       SelectNextTabPage();
                   createSQLScript("select " + topCount + " from gym_kind", "gym_kind", textBox22, checkBox11.Checked);
                   createSQLScript("select " + topCount + " from gym_item", "gym_item", textBox21, checkBox12.Checked);
                   createSQLScript("select " + topCount + " from gym_period", "gym_period", textBox20, checkBox13.Checked);
                   createSQLScript("select " + topCount + " from gym_reserve", "gym_reserve", textBox19, checkBox14.Checked);
                   createSQLScript("select " + topCount + " from mem_kind", "mem_kind", textBox18, checkBox15.Checked);
                   createSQLScript("select " + topCount + " from sys_identity", "sys_identity", textBox17, checkBox16.Checked);
                   createSQLScript("select " + topCount + " from mem_member", "mem_member", textBox16, checkBox17.Checked);

                   createSQLScript("select " + topCount + " from memcardlog", "memcardlog", textBox29, checkBox18.Checked);
                   createSQLScript("select " + topCount + " from fitness_monthfee", "fitness_monthfee", textBox15, checkBox19.Checked);
                   createSQLScript("select " + topCount + " from t_fsl_sk_student05", "t_fsl_sk_student05", textBox23, checkBox20.Checked);
                   createSQLScript("select " + topCount + " from fitness_enroll", "fitness_enroll", textBox24, checkBox21.Checked);
                   createSQLScript("select " + topCount + " from SnArt", "SnArt", textBox25, checkBox22.Checked);
                   createSQLScript("select " + topCount + " from fitness_classes", "fitness_classes", textBox26, checkBox23.Checked);
                   createSQLScript("select " + topCount + " from fitness_enroll_qchange", "fitness_enroll_qchange", textBox27, checkBox24.Checked);
                   createSQLScript("select " + topCount + " from fitness_cardchange", "fitness_cardchange", textBox28, checkBox25.Checked);
                   createSQLScript("select " + topCount + " from gym_status", "gym_status", textBox36, checkBox1.Checked);
                   if (checkBox2.Checked == true)
                       SelectNextTabPage();
                   createSQLScript("select " + topCount + " from card_times_info", "card_times_info", textBox39, checkBox2.Checked);
                   createSQLScript("select " + topCount + " from jxc_zb_sp_kc", "jxc_zb_sp_kc", textBox38, checkBox27.Checked);
                   createSQLScript("select " + topCount + " from jxc_bm_sp", "jxc_bm_sp", textBox37, checkBox28.Checked);
                   createSQLScript("select " + topCount + " from jxc_dj_kc_2", "jxc_dj_kc_2", textBox31, checkBox29.Checked);
                   createSQLScript("select " + topCount + " from jxc_dj_gh_2", "jxc_dj_gh_2", textBox35, checkBox30.Checked);
                   createSQLScript("select " + topCount + " from jxc_dj_xs_2", "jxc_dj_xs_2", textBox34, checkBox31.Checked);
                   createSQLScript("select " + topCount + " from jxc_dj_kc_db_2", "jxc_dj_kc_db_2", textBox33, checkBox32.Checked);
                   createSQLScript("select " + topCount + " from jxc_dj_kc_zc_2", "jxc_dj_kc_zc_2", textBox32, checkBox33.Checked);
                   createSQLScript("select " + topCount + " from jxc_bm_ck", "jxc_bm_ck", textBox30, checkBox26.Checked);
                   createSQLScript("select " + topCount + " from t_fsl_Entity1", "t_fsl_Entity1", textBox40, checkBox34.Checked);
                   createSQLScript("select " + topCount + " from t_fsl_Entity2", "t_fsl_Entity2", textBox41, checkBox35.Checked);

                   //完成后自动打开生成的脚本文件夹
                   if (Directory.Exists(Application.StartupPath + "\\脚本") != false)
                   {
                       string path = Application.StartupPath + "\\脚本"; System.Diagnostics.Process.Start("explorer.exe", path);
                   }
               });
            worker.IsBackground = true;
            worker.Start();
        }

        //private void button11_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from pos_bills", "pos_bills", textBox8);
        //}
        //private void button10_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from pos_sales", "pos_sales", textBox7);
        //}

        //private void button8_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from pos_bill_payment", "pos_bill_payment", textBox6);
        //}

        //private void button3_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from fitness_checkin", "fitness_checkin", textBox1);
        //}
        //private void button19_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from syskindacc", "syskindacc", textBox9);
        //}

        //private void button20_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from sys_payment", "sys_payment", textBox10);
        //}
        //private void button23_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from sys_user", "sys_user", textBox13);

        //}

        //private void button24_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from sys_workshop", "sys_workshop", textBox14);

        //}

        //private void button32_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from gym_kind", "gym_kind", textBox22);

        //}

        //private void button31_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from gym_item", "gym_item", textBox21);

        //}

        //private void button30_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from gym_period", "gym_period", textBox20);

        //}

        //private void button29_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from gym_reserve", "gym_reserve", textBox19);

        //}

        //private void button28_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from mem_kind", "mem_kind", textBox18);

        //}

        //private void button27_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from sys_identity", "sys_identity", textBox17);

        //}

        //private void button26_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from mem_member", "mem_member", textBox16);
        //}
        //private void button39_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from memcardlog", "memcardlog", textBox29);

        //}

        //private void button25_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from fitness_monthfee", "fitness_monthfee", textBox15);

        //}

        //private void button33_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from t_fsl_sk_student05", "t_fsl_sk_student05", textBox23);

        //}

        //private void button34_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from fitness_enroll", "fitness_enroll", textBox24);

        //}

        //private void button35_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from SnArt", "SnArt", textBox25);

        //}

        //private void button36_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from fitness_classes", "fitness_classes", textBox26);

        //}

        //private void button37_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from fitness_enroll_qchange", "fitness_enroll_qchange", textBox27);

        //}

        //private void button38_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from fitness_cardchange", "fitness_cardchange", textBox28);

        //}

        //private void button40_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from jxc_bm_ck", "jxc_bm_ck", textBox30);
        //}

        //private void button48_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from jxc_zb_sp_kc", "jxc_zb_sp_kc", textBox38);

        //}

        //private void button47_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from jxc_bm_sp", "jxc_bm_sp", textBox37);

        //}


        //private void button41_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from jxc_dj_kc_2", "jxc_dj_kc_2", textBox31);

        //}

        //private void button45_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from jxc_dj_gh_2", "jxc_dj_gh_2", textBox35);

        //}

        //private void button44_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from jxc_dj_xs_2", "jxc_dj_xs_2", textBox34);

        //}

        //private void button43_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from jxc_dj_kc_db_2", "jxc_dj_kc_db_2", textBox33);

        //}

        //private void button42_Click(object sender, EventArgs e)
        //{
        //    createSQLScript("select " + topCount + " from jxc_dj_kc_zc_2", "jxc_dj_kc_zc_2", textBox32);
        //}

        public void createSQLScript(string sql, string table_name, TextBox txtbox, bool isChecked)
        {
            if (isChecked == false)
            {
                return;
            }
            try
            {
                using (Database db = new Database())
                {
                    db.Connect(Sqlconn);

                    DataSet ds = db.GetSchema(sql);

                    int totalRecords = int.MaxValue;

                    //if (numericUpDownTotalRecords.Value > 0)
                    //{
                    //    totalRecords = (int)numericUpDownTotalRecords.Value;
                    //}

                    db.OpenDataReader(sql);

                    DataRow row = db.GetNextRow();
                    if (Directory.Exists(Application.StartupPath + "\\脚本") == false)
                    {
                        Directory.CreateDirectory(Application.StartupPath + "\\脚本");
                    }
                    //if (File.Exists(Application.StartupPath + "\\脚本\\" + table_name + ".sql") == false)
                    //{
                    //    File.Create(Application.StartupPath + "\\脚本\\" + table_name + ".sql");
                    //}
                    using (System.IO.FileStream fs = new System.IO.FileStream(Application.StartupPath + "\\脚本\\" + table_name + ".sql", System.IO.FileMode.Create,
                         System.IO.FileAccess.ReadWrite))
                    {
                        using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, Encoding.UTF8))
                        {

                            int current = 0;
                            while (row != null && totalRecords > 0)
                            {
                                totalRecords--;
                                string line = GetOneRowSql(ds, row, table_name);

                                db.Clear();

                                sw.WriteLine(line);
                                current++;
                                txtbox.Text = current.ToString();
                                Application.DoEvents();

                                row = db.GetNextRow();

                                if ((totalRecords % 100) == 0)
                                {
                                    GC.Collect();
                                }
                            }
                        }
                    }
                }

                richTextBox1.AppendText("成功创建:" + table_name + " sql脚本！\n\r");
                richTextBox1.ScrollToCaret();
            }
            catch (Exception e1)
            {
                richTextBox1.AppendText(DateTime.Now.ToLongTimeString() + ":" + e1.Message + "\n\r" + e1.StackTrace + "\n\r");
            }
        }
        string[] path = null;
        public long linecount = 0;
        private void button12_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("请检查服务器连接参数是否正确?\n\r" + textBoxConnectionString.Text + "\n\r正确点确定，否则取消", "友情提示", MessageBoxButtons.OKCancel);
            if (result != DialogResult.OK)
            {
                return;
            }

            //每次批量执行的sql条数
            int sql_count = (int)numericUpDownTotalRecords.Value;

            try
            {
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Title = "打开";
                ofd.Filter = "所有文件|*.sql";
                ofd.Multiselect = true;
                ofd.InitialDirectory = Application.StartupPath;
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    path = ofd.FileNames;
                }
                if (path == null)
                {
                    return;
                }
                Thread worker = new Thread(delegate()
                {
                    DateTime dt_read_txt = DateTime.Now;
                    StringBuilder sqls = new StringBuilder();
                    linecount = 0;
                    for (int i = 0; i < path.Length; i++)
                    {
                        using (StreamReader sr = new StreamReader(path[i]))
                        {

                            string line = string.Empty;
                            while ((line = sr.ReadLine()) != null)
                            {
                                if (line.Trim() != "")
                                {
                                    if (!line.EndsWith(";"))
                                    {
                                        sqls.Append(line);
                                        continue;
                                    }
                                    else
                                        sqls.Append(line + "\n");

                                    linecount++;
                                    labelCurrent.Text = "导入计数:" + linecount;
                                    if (linecount % sql_count == 0)
                                    {
                                        insertToDB(sqls);
                                        sqls.Remove(0, sqls.Length);
                                        GC.Collect();
                                    }
                                }
                            }
                            if (sqls.Length > 10)
                            {
                                insertToDB(sqls);
                            }
                            labelCurrent.Text = "导入计数:" + linecount;
                            sqls.Remove(0, sqls.Length);
                            GC.Collect();
                            richTextBox1.AppendText("时间:" + DateTime.Now.ToLongTimeString() + ":" + path[i] + "执行完毕！\n\r");
                        }
                    }
                });
                worker.IsBackground = true;
                worker.Start();
            }
            catch (Exception ex)
            {
                richTextBox1.AppendText(ex.Message + "\n\r" + ex.StackTrace);
            }
        }

        public void insertToDB(StringBuilder sb_sqls)
        {
            SqlConnection conn = new SqlConnection(textBoxConnectionString.Text);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();
            cmd.Transaction = tran;
            try
            {
                cmd.CommandText = sb_sqls.ToString();
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace);
                richTextBox1.AppendText(ex.Message + "\n\r" + ex.StackTrace);
                tran.Rollback();
                saveErrLog(sb_sqls.ToString(), "批量执行失败的sql");
            }
            finally
            {
                conn.Close();
                tran.Dispose();
                conn.Dispose();
            }
        }

        private void button13_Click(object sender, EventArgs e)
        {
            DialogResult Dloresult = MessageBox.Show("是否要清空此链接的账务数据?\n\r" + textBoxConnectionString.Text + "\n\r正确点确定，否则取消", "友情提示", MessageBoxButtons.OKCancel);
            if (Dloresult != DialogResult.OK)
            {
                return;
            }
            StringBuilder sb_sql = new StringBuilder();
            sb_sql.AppendLine(@"
 IF EXISTS ( SELECT 1
             FROM   sysobjects
             WHERE  name = 'fk_possalebills'
                    AND type = 'f ' )
    ALTER TABLE pos_sales  DROP CONSTRAINT fk_possalebills;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('pos_bills') AND name='timestampflag')
    alter table pos_bills DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('pos_sales') AND name='timestampflag')
    alter table pos_sales DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('pos_bill_payment') AND name='timestampflag')
    alter table pos_bill_payment DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('fitness_checkin') AND name='timestampflag')
    alter table fitness_checkin DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('syskindacc') AND name='timestampflag')
    alter table syskindacc DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('sys_payment') AND name='timestampflag')
    alter table sys_payment DROP COLUMN timestampflag;
IF EXISTS ( SELECT  1
                        FROM    sysindexes
                        WHERE   name = 'PK_fitness_cardchange' )
                ALTER TABLE fitness_cardchange DROP CONSTRAINT PK_fitness_cardchange;
            TRUNCATE TABLE sys_workshop;
            TRUNCATE TABLE sys_user;
            TRUNCATE TABLE sys_payment;
            TRUNCATE TABLE syskindacc;
            TRUNCATE TABLE fitness_checkin;
            TRUNCATE TABLE pos_bill_payment;
            TRUNCATE TABLE pos_sales;
            TRUNCATE TABLE pos_bills;
            TRUNCATE TABLE memcardlog;
            TRUNCATE TABLE mem_member;
            TRUNCATE TABLE sys_identity;
            TRUNCATE TABLE mem_kind;
            TRUNCATE TABLE gym_reserve;
            TRUNCATE TABLE gym_period;
            TRUNCATE TABLE gym_item;
            TRUNCATE TABLE gym_status;
            TRUNCATE TABLE fitness_cardchange;
            TRUNCATE TABLE fitness_enroll_qchange;
            TRUNCATE TABLE fitness_classes;
            TRUNCATE TABLE SnArt;
            TRUNCATE TABLE fitness_enroll;
            TRUNCATE TABLE t_fsl_sk_student05;
            TRUNCATE TABLE fitness_monthfee;
            TRUNCATE TABLE gym_kind;
            TRUNCATE TABLE card_times_info;
                    --删除表后创建
            DROP TABLE sys_payment;
            CREATE TABLE [dbo].[sys_payment]
                (
                  [code] [nvarchar](20) NOT NULL ,
                  [name_e] [nvarchar](50) NULL ,
                  [name_c] [nvarchar](50) NULL ,
                  [if_account] [nchar](1) NULL ,
                  [if_efficieny] [nchar](1) NULL ,
                  [account_code] [nvarchar](50) NULL ,
                  [type] [int] NULL ,
                  [memo] [nvarchar](200) NULL ,
                  [parent_code] [nvarchar](20) NULL ,
                  [money_type] [nvarchar](50) NULL ,
                  [payment_type] [nchar](1) NULL ,
                  [bank_service] [decimal](18, 4) NULL ,
                  [parent_type] [nvarchar](10) NULL ,
                  [detail_type] [nvarchar](20) NULL ,
                  [if_sat] [nchar](1) NULL ,
                  [type_workshop] [nvarchar](200) NULL ,
                  [type_code] [nvarchar](200) NULL ,
                  [touch_disp] [nvarchar](30) NULL ,
                  [fEntityNo] [varchar](10) NULL ,
                  [FClub] [varchar](12) NULL ,
                  [Fpcno] [varchar](10) NULL ,
                  [fcreatedate] [datetime] NULL ,
                  [fmodifydate] [datetime] NULL ,
                  [fUserNo] [varchar](8) NULL ,
                  [n_last] [numeric](18, 0) NULL ,
                  [ifdown] [char](1) NULL ,
                  [fcoupinput] [int] NULL ,
                  [fifrepacc] [int] NULL ,
                  CONSTRAINT [PK_sys_payment] PRIMARY KEY CLUSTERED ( [code] ASC )
                    WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                           IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                           ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
                )
            ON  [PRIMARY];
            ALTER TABLE sys_payment  ADD fbillpos INT NULL DEFAULT (0);
            ALTER TABLE pos_bills ALTER COLUMN paymethod VARCHAR(20);
            ALTER TABLE pos_bills ALTER COLUMN saleshop NVARCHAR(20);
            ALTER TABLE pos_bills ALTER COLUMN payshop NVARCHAR(20);
            ALTER TABLE pos_bills ALTER COLUMN guest_identity NVARCHAR(20);
            ALTER TABLE pos_bills ALTER COLUMN billshop NVARCHAR(20);
            ALTER TABLE pos_sales ALTER COLUMN closemethod VARCHAR(20); 
            ALTER TABLE pos_sales ALTER COLUMN paymethod VARCHAR(20);
            ALTER TABLE pos_sales ALTER COLUMN billshop NVARCHAR(20);
            ALTER TABLE pos_sales ALTER COLUMN sale_kinds VARCHAR(20);  
            ALTER TABLE pos_sales ALTER COLUMN parent_kinds VARCHAR(20);  
            ALTER TABLE pos_sales ALTER COLUMN sale_category VARCHAR(20); 
            ALTER TABLE pos_sales ALTER COLUMN parent_category VARCHAR(20);
            ALTER TABLE pos_sales ALTER COLUMN saleshop NVARCHAR(20);
            ALTER TABLE pos_sales ALTER COLUMN payshop NVARCHAR(20);
            ALTER TABLE pos_bill_payment ALTER COLUMN paymethod VARCHAR(20);  
            ALTER TABLE pos_bill_payment ALTER COLUMN payparent VARCHAR(20); 
            ALTER TABLE pos_bill_payment ALTER COLUMN payshop NVARCHAR(20);
            ALTER TABLE fitness_checkin ALTER COLUMN memiden NVARCHAR(20); 
            ALTER TABLE fitness_checkin ALTER COLUMN workshop VARCHAR(20);
            ALTER TABLE dbo.fitness_checkin ALTER COLUMN cname NVARCHAR(30) NULL;
            ALTER TABLE dbo.fitness_checkin ALTER COLUMN cardno NVARCHAR(20) NULL;
            ALTER TABLE gym_kind ALTER COLUMN shop_code VARCHAR(20);
            ALTER TABLE mem_kind ALTER COLUMN icno VARCHAR(20);
            ALTER TABLE mem_member ALTER COLUMN mem_iden NVARCHAR(20);
            ALTER TABLE mem_member ALTER COLUMN tickh NVARCHAR(30) NULL;
            ALTER TABLE mem_member ALTER COLUMN main_id NVARCHAR(20);
            ALTER TABLE mem_member ALTER COLUMN billtocard NVARCHAR(20);
            ALTER TABLE mem_member ALTER COLUMN m_id NVARCHAR(30) NOT NULL;
            ALTER TABLE mem_member ALTER COLUMN sys_id NUMERIC NULL;
            ALTER TABLE memcardlog ALTER COLUMN timeitem VARCHAR(30);
            ALTER TABLE memcardlog ALTER COLUMN workshop VARCHAR(20);
            ALTER TABLE SYS_USER ALTER COLUMN WORKSHOP_ID NVARCHAR(20);
            ALTER TABLE dbo.SYS_USER ALTER COLUMN ID INT NULL;
            ALTER TABLE t_fsl_sk_student05 ALTER COLUMN v_artModeNo VARCHAR(20) NULL;
            ALTER TABLE t_fsl_sk_student05 ALTER COLUMN v_bcname VARCHAR(30);
            IF EXISTS ( SELECT  1
                        FROM    sysobjects
                        WHERE   name = 'FK_SnArt_SnArtGroup '
                                AND type = 'f ' )
                ALTER TABLE SnArt DROP CONSTRAINT FK_SnArt_SnArtGroup;
            IF EXISTS ( SELECT  1
                        FROM    sysindexes
                        WHERE   name = 'mem_member_tickhx' )
                DROP INDEX mem_member_tickhx ON mem_member;
            IF EXISTS ( SELECT  1
                        FROM    sysindexes
                        WHERE   name = 'IX_mem_memberacda' )
                ALTER TABLE mem_member DROP CONSTRAINT IX_mem_memberacda;
            ALTER TABLE sys_identity ALTER COLUMN code NVARCHAR(20);
            ALTER TABLE dbo.gym_reserve ALTER COLUMN cname NVARCHAR(50) NULL;
            ALTER TABLE dbo.fitness_cardchange ALTER COLUMN sid INT NULL;
");
            //insertToDB(sb_sql);
            int result = 0;
            SqlConnection conn = new SqlConnection(textBoxConnectionString.Text);
            try
            {
                SqlCommand cmd = new SqlCommand(sb_sql.ToString(), conn);
                cmd.CommandTimeout = 50000;
                conn.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                saveErrLog(sb_sql.ToString() + ex.Message, "sql_err");
                MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace);
            }

            finally
            {
                conn.Close();
                conn.Dispose();
            }
            if (result == -1)
                MessageBox.Show("清除成功！");

        }

        private void button21_Click(object sender, EventArgs e)
        {
            DialogResult Dloresult = MessageBox.Show("将本客户端编号设置为:" + txtLocalId.Text + "?\n\r ps:需要对应服务器设置的编号 \n\r yes点确定，no取消", "友情提示", MessageBoxButtons.OKCancel);
            if (Dloresult != DialogResult.OK)
            {
                return;
            }
            string sql = @"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t_SyncFinancialRecords]') AND type in (N'U'))
                        BEGIN
                        CREATE TABLE [dbo].[t_SyncFinancialRecords](
	                        [sync_id] [varchar](40) NOT NULL,
	                        [club_id] [numeric](18, 0) NOT NULL,
	                        [sync_type] [char](2) NOT NULL,
	                        [data_type] [varchar](30) NULL,
	                        [fdate] [datetime] NULL,
	                        [ftimestamp] [varchar](26) NULL,
	                        [fstatus] [int] NULL,
	                        [fjson] [text] NULL,
	                        [fdemo] [varchar](26) NULL,
	                        [SendCounts] [int] NULL,
	                        [LastSendTimes] [datetime] NULL,
                         CONSTRAINT [t_SyncFinancialRecordsaa_key_x] PRIMARY KEY CLUSTERED 
                        (
	                        [sync_id] ASC
                        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
                        END;";
            sql += " TRUNCATE TABLE t_SyncFinancialRecords;";
            sql += "update SYS_COMPANY set LocalId='" + txtLocalId.Text.Trim() + "';";
            sql += @"IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('pos_bills') AND name='timestampflag')
    alter table pos_bills DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('pos_sales') AND name='timestampflag')
    alter table pos_sales DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('pos_bill_payment') AND name='timestampflag')
    alter table pos_bill_payment DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('fitness_checkin') AND name='timestampflag')
    alter table fitness_checkin DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('syskindacc') AND name='timestampflag')
    alter table syskindacc DROP COLUMN timestampflag;
    IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('sys_payment') AND name='timestampflag')
    alter table sys_payment DROP COLUMN timestampflag;";
            try
            {
                if (exec_sql(sql) > 0)
                {
                    MessageBox.Show("设置成功!");
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message + "\n\r" + ex.StackTrace);
            }

        }

        private void button22_Click(object sender, EventArgs e)
        {
            createSQLScript(richTextBox2.Text, textBox12.Text, textBox11, true);
        }

        private void button46_Click(object sender, EventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            foreach (Control item in tabPage3.Controls)
            {
                if (item is CheckBox)
                {
                    ((CheckBox)item).Checked = true;
                }
            }
            foreach (Control item in tabPage6.Controls)
            {
                if (item is CheckBox)
                {
                    ((CheckBox)item).Checked = true;
                }
            }
            foreach (Control item in tabPage7.Controls)
            {
                if (item is CheckBox)
                {
                    if (((CheckBox)item).Enabled == false)
                        continue;
                    ((CheckBox)item).Checked = true;
                }
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            foreach (Control item in tabPage3.Controls)
            {
                if (item is CheckBox)
                {
                    ((CheckBox)item).Checked = false;
                }
            }
            foreach (Control item in tabPage6.Controls)
            {
                if (item is CheckBox)
                {
                    ((CheckBox)item).Checked = false;
                }
            }
            foreach (Control item in tabPage7.Controls)
            {
                if (item is CheckBox)
                {
                    ((CheckBox)item).Checked = false;
                }
            }
        }

        private void tabControl2_MouseDoubleClick(object sender, MouseEventArgs e)
        {

            foreach (Control item in tabControl2.SelectedTab.Controls)
            {
                if (item is CheckBox)
                {
                    if (((CheckBox)item).Checked == true)
                        ((CheckBox)item).Checked = false;
                    else
                    {
                        if (((CheckBox)item).Enabled == false)
                            continue;
                        ((CheckBox)item).Checked = true;
                    }
                }
            }
        }

        private void button10_Click(object sender, EventArgs e)
        {
            string[] tableArray = richTextBox3.Text.Split('\n');
            for (int i = 0; i < tableArray.Length; i++)
            {
                if (tableArray[i].Trim() != "")
                {
                    saveErrLog(createAuditTable(tableArray[i])+"\nGO\n"+createTrigger(tableArray[i], ""), tableArray[i],"sql");
                   
                }
            }
            //完成后自动打开生成的脚本文件夹
            if (Directory.Exists(Application.StartupPath + "\\审计") != false)
            {
                string path = Application.StartupPath + "\\审计"; System.Diagnostics.Process.Start("explorer.exe", path);
            }
            //MessageBox.Show(createAuditTable("pos_sales"));
            //MessageBox.Show(createTrigger("pos_sales", ""));
        }
        public DataTable getTableDesign(string table_name)
        {
            getsqlconn();
            SqlConnection conn = new SqlConnection(Sqlconn);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = @"SELECT   d.name
                表名 ,
        a.colorder 字段序号 ,
        a.name 字段名 ,
        ( CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√'
               ELSE ''
          END ) 标识 ,
        ( CASE WHEN ( SELECT    COUNT(*)
                      FROM      sysobjects
                      WHERE     ( NAME IN (
                                  SELECT    NAME
                                  FROM      sysindexes
                                  WHERE     ( id = a.id )
                                            AND ( indid IN (
                                                  SELECT    indid
                                                  FROM      sysindexkeys
                                                  WHERE     ( id = a.id )
                                                            AND ( colid IN (
                                                              SELECT
                                                              colid
                                                              FROM
                                                              syscolumns
                                                              WHERE
                                                              ( id = a.id )
                                                              AND ( NAME = a.name ) ) ) ) ) ) )
                                AND ( xtype = 'PK' )
                    ) > 0 THEN '√'
               ELSE ''
          END ) 主键 ,
        b.name 类型 ,
        a.length 占用字节数 ,
        COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度 ,
        ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数 ,
        ( CASE WHEN a.isnullable = 1 THEN '√'
               ELSE ''
          END ) 允许空 ,
        ISNULL(e.text, '') 默认值
       FROM    syscolumns a
        LEFT JOIN systypes b ON a.xtype = b.xusertype
        INNER JOIN sysobjects d ON a.id = d.id
                                   AND d.xtype = 'U'
                                   AND d.name <> 'dtproperties'
        LEFT JOIN syscomments e ON a.cdefault = e.id ";
            cmd.CommandText += (" WHERE d.name ='" + table_name + "'");

            cmd.CommandText += " ORDER BY a.id ,a.colorder";
            SqlDataAdapter huoche = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            huoche.Fill(dt);
            return dt;
        }
        public string createAuditTable(string table_name)
        {
            var sqlText = new StringBuilder();
            DataRow[] datarows = getTableDesign(table_name).Select();
            sqlText.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Audit_" + table_name + "]'))");
            sqlText.AppendLine("drop table [dbo].[Audit_" + table_name + "]");
            sqlText.AppendLine("GO");
            sqlText.AppendLine("CREATE TABLE [dbo].[Audit_" + table_name + "](");
            sqlText.AppendLine("[auditID] [numeric](18, 0) PRIMARY KEY IDENTITY(1,1) NOT NULL,");
            sqlText.AppendLine("[action] [varchar](50) NULL,");
            sqlText.AppendLine("[from] [varchar](100) NULL,");
            sqlText.AppendLine("[auditTimestamp] [datetime] NULL,");
            foreach (DataRow item in datarows)
            {
                var type=item["类型"].ToString();
                if (type.Contains("ar"))
                {
                    sqlText.AppendLine("[" + item["字段名"] + "] [" + type + "](" + item["长度"] + ") NULL,");
                }
                else if (type.Contains("dec") || type.Contains("num"))
                {
                    sqlText.AppendLine("[" + item["字段名"] + "] [" + type + "](" + item["长度"] + "," + item["小数位数"] + ") NULL,");
                }
                else
                {
                    sqlText.AppendLine("[" + item["字段名"] + "] [" + type + "] NULL,");
                }
            }
            //去掉最后一个逗号
            //逗号位置
            var index= sqlText.ToString().LastIndexOf(",");
            if (index != -1)
            {
                var sb = sqlText.ToString().Substring(0, index);
                sqlText.Length = 0;
                sqlText.Append(sb + ")");
            }

            return sqlText.ToString();
        }
        public string createTrigger(string table_name, string audit_table_name)
        {
            //合成的字段
            var sbField = new StringBuilder();

            //创建触发器的sql
            var sb_all = new StringBuilder();

            DataRow[] datarows = getTableDesign(table_name).Select();
            foreach (DataRow item in datarows)
            {
                sbField.Append(item["字段名"] + ",");
            }
            //去掉最后一个逗号
            sbField.Length = sbField.Length - 1;
            sb_all = build(table_name, 0, sbField);
            sb_all.AppendLine( build(table_name, 1, sbField).ToString());
            sb_all.AppendLine(build(table_name, 2, sbField).ToString());
            return sb_all.ToString();
        }
        public StringBuilder build(string table_name, int type, StringBuilder sbField)
        {
            StringBuilder sb = new StringBuilder();
            string action = string.Empty;
            string insert_sql = string.Empty;
            switch (type)
            {
                case 0: { action = "INSERT"; insert_sql = "INSERT INTO Audit_" + table_name + " SELECT '" + action + "',@from,GETDATE()," + sbField + " FROM INSERTED"; } break;
                case 1: { action = "UPDATE"; insert_sql = "INSERT INTO Audit_" + table_name + " SELECT '" + action + "',@from,GETDATE()," + sbField + " FROM INSERTED"; } break;
                case 2: { action = "DELETE"; insert_sql = "INSERT INTO Audit_" + table_name + " SELECT '" + action + "',@from,GETDATE()," + sbField + " FROM DELETED"; } break;
            }
            sb.AppendLine("IF EXISTS(select * from sysobjects where type='tr' and name='TGR_" + table_name + "_" + action + "')");
            sb.AppendLine("DROP  TRIGGER TGR_" + table_name + "_" + action);
            sb.AppendLine("GO");
            sb.AppendLine("CREATE TRIGGER TGR_" + table_name + "_" + action);
            sb.AppendLine("ON [" + table_name + "] FOR " + action);
            sb.AppendLine("AS");
            sb.AppendLine("BEGIN");
            sb.AppendLine("DECLARE @from VARCHAR(100)");
            sb.AppendLine("SELECT @from=(SELECT '客户机名:'+rtrim(hostname)+' 程序名:'+program_name FROM master..sysprocesses WHERE spid=(SELECT @@SPID))");
            sb.AppendLine(insert_sql);
            sb.AppendLine("End");
            sb.AppendLine("GO");
            //action = null;
            //insert_sql = null;
            return sb;
        }
    }
}
